package itsm.isperp.report.data.warehouse;

import itsm.isperp.framework.core.context.ContextHolder;
import itsm.isperp.framework.data.ObjectSqlSource;

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import me.chanjar.weixin.common.util.StringUtils;

import org.springframework.data.jdbc.support.DatabaseType;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * 
 * 
 * 
 * @author lizx
 * @date 2015-8-24
 * @since 1.0
 * 
 */
public class QueryRequest implements ObjectSqlSource {

	List<String> columns = new LinkedList<>();

	Map<String, String> innerJoin = new LinkedHashMap<>();

	String from;

	String where;

	String groupBy;

	String orderBy;

	String group;

	public QueryRequest() {

	}

	public String getGroup() {
		return group;
	}

	public void setGroup(String group) {
		this.group = group;
	}

	public String getOrderBy() {
		return orderBy;
	}

	public void setOrderBy(String orderBy) {
		this.orderBy = orderBy;
	}

	public String getGroupBy() {
		return groupBy;
	}

	public void setGroupBy(String groupBy) {
		this.groupBy = groupBy;
	}

	public QueryRequest(String from, String where, String... columns) {
		this.from = from;
		this.where = where;
		for (String s : columns)
			this.columns.add(s);
	}

	public void addColumn(String column) {
		this.columns.add(column);
	}

	public void setInnerJoin(Map<String, String> innerJoin) {
		this.innerJoin = innerJoin;
	}

	public void addInnerJoin(String table, String on) {
		this.innerJoin.put(table, on);
	}

	public String getFrom() {
		return from;
	}

	public void setFrom(String from) {
		this.from = from;
	}

	public String getWhere() {
		return where;
	}

	public void setWhere(String where) {
		this.where = where;
	}

	public List<String> getColumns() {
		List<String> cols = new ArrayList<>();
		for (String c : columns) {
			int asi = c.indexOf(" AS ");
			if (asi > 0) {
				cols.add(c.substring(asi + 4).trim());
			} else {
				cols.add(c);
			}

		}
		return cols;
	}

	public void setColumns(List<String> columns) {
		this.columns = columns;
	}

	@Override
	public String getSql() {
		String cs = "";
		for (String c : columns) {
			cs += "," + c;
		}
		String inner = "";
		for (String c : innerJoin.keySet()) {
			inner += " INNER JOIN " + c + " ON  " + innerJoin.get(c);
		}

		StringBuilder sb = new StringBuilder();

		sb.append("SELECT ").append(cs.substring(1)).append(" FROM ")
				.append(from).append("   ").append(inner).append(" WHERE 1=1 ")
				.append(where);
		if (StringUtils.isNotEmpty(groupBy)) {
			sb.append(" GROUP BY ").append(groupBy);
		}

		boolean g = false;
		if (StringUtils.isNotEmpty(orderBy)) {
			g = true;
			sb.append(" ORDER BY ").append(orderBy);

		}

		if (StringUtils.isNotEmpty(group)) {
			if (g)
				sb.append(",");
			else
				sb.append(" ORDER BY ");

			if (itsm.isperp.framework.data.dialect.DialectFactory
					.getDatabaseType() == DatabaseType.ORACLE) {
				sb.append("nlssort(" + group
						+ ", 'NLS_SORT=SCHINESE_PINYIN_M')");
			} else {
				sb.append(group);
			}
		}

		return sb.toString();
	}

	public List<Map<String, Object>> query() {
		JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
		String s = getSql();
		return jdbcTemplate.queryForList(s);
	}

	public List<String> queryOneColumn() {
		JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
		return jdbcTemplate.queryForList(getSql(), String.class);
	}
}
